Upgrading Existing Applications to Support Automatic ID Lookup

Small filter panels

Filter panels are typically designed to be as small as possible vertically, as to maximize the available space for filtered content. When Automatic ID Lookup is enabled, you may not realize that the lookup value is actually displayed below the ID field. The panel will display a scroll bar, but it is not easy to see, and requires additional scrolling action to see the value:   

In such views, increase the layout region height by 15-20 pixels per ID field, so that there is space to display lookup values without scrolling:

Unqualified field names in SQL  

Some views have data sources with SQL restrictions or SQL virtual fields that do not fully qualify field names. For example:

<field name= "bl_pct_gp"  dataType= "number"  decimals= "2" >
<sql dialect= "generic" >area_gp - area_gp_dp - area_gp_comn</sql>
</field>

These unqualified field names may conflict with field that have the same name in the lookup table. To resolve the conflict, add table name qualifiers:

<field name= "bl_pct_gp"  dataType= "number"  decimals= "2" > 
<sql dialect= "generic" >bl.area_gp - bl.area_gp_dp - bl.area_gp_comn</sql> 
</field>

Similarly, if applying SQL parameters or restrictions from client-side JavaScript code (which is not a recommended practice), use fully qualified field names.

Concatenating fields in SQL

Some charts and reports concatenate fields in SQL queries or SQL virtual fields. For example:

These SQL fragments cannot be automatically altered to display lookup fields. To support lookup in such reports, replace literal SQL with the new sql.lookupConcatFields expression:

The query will then return ID and/or lookup fields, depending on whether ID lookup is enabled for the project and for specific schema tables.

If the data source does not already contain corresponding ID fields, make sure to add them:

<field name= "dv_id"  hidden= "true" />
<field name= "dp_id"  hidden= "true"

Auto-complete

Filter consoles that use virtual ID fields need to add virtual name fields as well, in order for auto-complete to display both ID and name. For example, the Create New Survey form in the Manage Asset and Equipment Survey view allows users to enter Building Code to automatically add survey task for equipment in a specific building. The Survey table does not have the Building Code field, so the form defines a virtual field, and sends its value to a workflow rule that creates survey tasks (ab-eq-survey-new.axvw):

The form also defines a custom Select Value command, since virtual fields do not validate on any table:

<command type= "selectValue"
fieldNames= "survey.bl_id"
selectFieldNames= "bl.bl_id"
visibleFieldNames= "bl.bl_id,bl.name" > <title>Building</title>
</command>

To support auto-complete on Building Name, add another virtual field to the data source, and modify the Select Value command:

<field table= "survey"  name= "bl_name"  dataType= "text"  size= "64" /> <command type= "selectValue"
         fieldNames= "survey.bl_id,survey.bl_name"
       selectFieldNames= "bl.bl_id,bl.name"
    visibleFieldNames= "bl.bl_id,bl.name" >
<title>Building</title>

</command>